﻿using Dapper;
using System.Collections.Generic;
using zhangmenren.Db;
using zhangmenren.Models;
using zhangmenren.ViewModel;

namespace zhangmenren.Repository
{
	public class ApplyRepository : BaseRepository
	{
		public int Add(ApplyEntity entity)
		{
			string sql = @"INSERT INTO Apply 
                            (UserId
                            ,Name
                            ,Duties,Period,Project,Tel,WX,State,AddDT)
                        VALUES
                            (@UserId
                            ,@Name
                            ,@Duties,@Period,@Project,@Tel,@WX,0,GETDATE())";
			return Execute(connection => connection.Execute(sql, entity));
		}

		/// <summary>
		/// 根据用户标识查询申请
		/// </summary>
		/// <param name="userId"></param>
		/// <returns></returns>
		public IEnumerable<ApplyEntity> FindByUserId(int userId)
		{
			string sql = @"SELECT UserId
                            ,Name
                            ,Duties,Period,Project,Tel,WX,State,AddDT FROM Apply WHERE UserId=@UserId ORDER BY State";
			return Execute(connection => connection.Query<ApplyEntity>(sql, new { UserId = userId }));
		}

        public IEnumerable<ApplyModel> Select()
        {
            string sql = @"SELECT Id,UserId
                            ,Name
                            ,Duties,Period,Project,Tel,WX,State,AddDT,(SELECT UserName FROM [User] WHERE [User].ID=Apply.UserId) AS UserName FROM Apply ORDER BY State";
            return Execute(connection => connection.Query<ApplyModel>(sql));
        }

      public int UpState(int id, int state)
        {
            string sql = @"UPDATE [Apply] SET State = @State WHERE Id = @Id";
            return Execute(connection => connection.Execute(sql, new { State = state, Id = id }));  //Items = items.Skip((pageIndex - 1) * pageSize).Take(pageSize)
        }

        public int Del(int id)
        {
            string sql = @"DELETE FROM [Apply] WHERE Id = @Id";
            return Execute(connection => connection.Execute(sql, new { Id = id }));
        }

    }
}
